select
  md.mdb_source_id as mdbid,
  tl.tables_loaded,
  tc.tables_computed,
  datetime(tl.timestamp, 'unixepoch', 'localtime') as timestamp_loaded,
  datetime(tc.timestamp, 'unixepoch', 'localtime') as timestamp_computed,
  tl.timestamp is not null as loaded,
  tc.timestamp is not null as computed,
  coalesce(md.status, 'active') as status,
  md.'urls.latest' as url,
  md.'location.bounding_box.minimum_latitude' as min_lat,
  md.'location.bounding_box.minimum_longitude' as min_lon,
  md.'location.bounding_box.maximum_latitude' as max_lat,
  md.'location.bounding_box.maximum_longitude' as max_lon,
  json_array(
    md.'location.bounding_box.minimum_latitude',
    md.'location.bounding_box.minimum_longitude',
    md.'location.bounding_box.maximum_latitude',
    md.'location.bounding_box.maximum_longitude'
  ) as bbox,
  6371.0 * acos(
    sin(90.0 - md.'location.bounding_box.minimum_latitude' * pi() / 180.0) *
    sin(90.0 - md.'location.bounding_box.maximum_latitude' * pi() / 180.0) *
    cos(
      (md.'location.bounding_box.minimum_longitude' * pi() / 180.0) - 
      (md.'location.bounding_box.maximum_longitude' * pi() / 180.0)
    ) +
    cos(90.0 - md.'location.bounding_box.minimum_latitude' * pi() / 180.0) *
    cos(90.0 - md.'location.bounding_box.maximum_latitude' * pi() / 180.0)
  ) as km,
  md.'location.subdivision_name' as subdivision,
  md.'location.country_code' as country,
  md.'location.municipality' as municipality,
  md.provider,
  md.name
from
  (select * from _mdb where data_type = 'gtfs' and 'urls.latest' is not null) md
  left join (select source, group_concat(tbl) as tables_loaded, * from _mobsql where tbl not like '_cv%' group by source) tl
    on md.mdb_source_id = tl.source and tl.source != -1
  left join (
    select source, group_concat(tbl || iif(source_dual is not null, ":" || source_dual, "")) as tables_computed, timestamp,
    *
    from (
      select
        source, tbl, timestamp,
        iif(source is not null and source_pair is not null, source || ":" || source_pair, null) as source_dual
      from _mobsql
      where tbl like '_cv%'
      union
      select
        source_pair, tbl, timestamp,
        iif(source is not null and source_pair is not null, source || ":" || source_pair, null) as source_dual
      from _mobsql
      where tbl like '_cv%'
    ) g
    group by source
  ) tc
    on md.mdb_source_id = tc.source and tc.source != -1
